﻿using System;
using System.Collections.Generic;

using System.Web;

using System.Data.SqlClient;
using System.Data;

namespace FSViet.Survey.Helpers
{
	public class DataHelper
	{
		public SqlConnection CreateConnection()
		{
			return new SqlConnection(GlobalApplication.ConnectionStringSetting.ConnectionString);
		}
		#region exection non query

		public int Execute(string sql,params SqlParameter[] sqlParameters)
		{
			int result = 0;
			SqlConnection conn =CreateConnection();
			conn.Open();
			try
			{
				result = Execute(conn, sql, sqlParameters);
			}
			finally
			{
				conn.Close();
			}
			return result;
		}

		public int Execute(SqlConnection conn, string sql,params SqlParameter[] sqlParameters)
		{
			var cmd = new SqlCommand(sql, conn);
			if (sqlParameters != null)
				foreach (SqlParameter item in sqlParameters)
					cmd.Parameters.Add(((ICloneable)item).Clone());
			return cmd.ExecuteNonQuery();
		}

		#endregion

		#region exection non query

		public object ExecuteScalar(string sql, params SqlParameter[] sqlParameters)
		{
			object result = null;
			SqlConnection conn =CreateConnection();
			conn.Open();
			try
			{
				result = ExecuteScalar(conn, sql, sqlParameters);
			}
			finally
			{
				conn.Close();
			}
			return result;
		}

		public object ExecuteScalar(SqlConnection conn, string sql, params SqlParameter[] sqlParameters)
		{
			var cmd = new SqlCommand(sql, conn);
			if (sqlParameters != null)
				foreach (SqlParameter item in sqlParameters)
					cmd.Parameters.Add(((ICloneable)item).Clone());
			return cmd.ExecuteScalar();
		}

		#endregion

		#region exection fill data

		public DataTable Load(string sql, int startRecord, int maxRecord, params SqlParameter[] sqlParameters)
		{
			int totalRecord = 0;
			return Load("",sql, startRecord, maxRecord, out totalRecord, sqlParameters);
		}
		public DataTable Load(string countSql, string selectSql, int startRecord, int maxRecord,out int totalRecord, params SqlParameter[] sqlParameters)
		{
			totalRecord = 0;
			SqlConnection conn = CreateConnection();
			conn.Open();
			var result = new DataTable();
			try
			{
				result = Load(conn, countSql, selectSql, startRecord, maxRecord, out totalRecord, sqlParameters);
			}
			finally
			{
				conn.Close();
			}

			return result;
		}

		public DataTable Load(SqlConnection conn, string countSql, string selectSql, int startRecord, int maxRecord,out int totalRecord, params SqlParameter[] sqlParameters)
		{
			totalRecord = 0;
			var result = new DataTable();
				if (countSql.Trim().Length>0)
					totalRecord = (int)ExecuteScalar(conn, countSql, sqlParameters);

				result = Load(conn, selectSql, startRecord, maxRecord, sqlParameters);

			return result;
		}

		public DataTable Load(SqlConnection conn, string sql, int startRecord, int maxRecord,params SqlParameter[] sqlParameters)
		{
			var result = new DataTable();
			var cmd = new SqlCommand(sql, conn);
			if (sqlParameters != null)
				foreach (SqlParameter item in sqlParameters)
					cmd.Parameters.Add(((ICloneable)item).Clone());

			var da = new SqlDataAdapter(cmd);
			da.Fill(startRecord, maxRecord, result);
			return result;
		}

		#endregion

		public SqlParameter CreateParam(string paramName, SqlDbType dataType, object value)
		{
			var result = new SqlParameter(paramName, dataType);
			result.Value = value;
			return result;
		}
	}

	
}